package com.mp.approvalreq.dao.impl;


import com.mp.approvalreq.dao.RedDao;
import com.mp.approvalreq.entity.MessageRead;
import com.mp.approvalreq.entity.common.Constants;
import com.mp.approvalreq.entity.dto.RedPointDTO;
import com.plusmoney.util.DateTimeHelper;
import com.plusmoney.util.SQLLogger;
import com.plusmoney.util.Utils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class RedDaoImpl implements RedDao {
    private static final Logger logger = LoggerFactory.getLogger(RedDaoImpl.class);
    private Connection conn;

    public RedDaoImpl(Connection conn) {
        this.conn = conn;
    }

    @Override
    public void deleteRedPointByRequestId(Integer requestId) throws SQLException {
        String sql = " update request_message_read set Fis_read = 1, Fread_time = now() where Frequest_id = ? and Fis_read = 0";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, requestId);
            logger.info(SQLLogger.logSQL(sql, requestId));

            ps.executeUpdate();
        }
    }


    @Override
    public List<Integer> getAllRedPointRequestIds(Integer companyInfoId) throws SQLException {
        List<Integer> requestIds = new LinkedList<>();

        String sql = " select Frequest_id from request_message_read where Fcompany_info_id = ? and Fis_read = 0 group by Frequest_id ";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    requestIds.add(rs.getInt(1));
                }
            }
        }

        return requestIds;
    }


    @Override
    public List<RedPointDTO> getUserRedPointList(Integer companyInfoId) throws SQLException {
        List<RedPointDTO> redPointDTOs = new LinkedList<>();

        String sql = " select Frequest_id, Fis_refuse, Fis_at_red " +
                " from approval.request_message_read " +
                " where Fcompany_info_id = ? and Fis_read = 0 ";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, companyInfoId);

            logger.info(SQLLogger.logSQL(sql, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    RedPointDTO redPointDTO = new RedPointDTO();
                    redPointDTO.setRequestId(rs.getInt(1));
                    redPointDTO.setRefuseRed(rs.getInt(2));
                    redPointDTO.setAtRed(rs.getInt(3));

                    redPointDTOs.add(redPointDTO);
                }
            }
        }
        return redPointDTOs;
    }

    @Override
    public Map<Integer, List<MessageRead>> selectUserRedPointMap(Integer companyInfoId) throws SQLException {
        Map<Integer, List<MessageRead>> map = new HashMap<>();
        if (Utils.isNull(companyInfoId) || companyInfoId == 0) {
            return map;
        }
        String sql = "select Fmessage_id, Fcompany_info_id, Frequest_id, Fis_refuse, Fis_at_red"
                + " from request_message_read"
                + " where Fcompany_info_id = ?  and Fis_read = 0";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    MessageRead messageRead = new MessageRead();
                    messageRead.setMessageId(rs.getInt(1));
                    messageRead.setCompanyInfoId(rs.getInt(2));
                    messageRead.setRequestId(rs.getInt(3));
                    messageRead.setIsRefuse(rs.getInt(4));
                    messageRead.setIsAtRed(rs.getInt(5));

                    if (!map.containsKey(messageRead.getRequestId())) {
                        map.put(messageRead.getRequestId(), new ArrayList<>());
                    }
                    map.get(messageRead.getRequestId()).add(messageRead);
                }
            }
        }
        return map;
    }

    @Override
    public List<MessageRead> selectRequestUserRedPoint(Integer requestId, Integer companyInfoId) throws SQLException {
        List<MessageRead> list = new ArrayList<>();
        String sql = "select Fmessage_id, Fcontact_id, Fcompany_info_id, Frequest_id, Fis_refuse,"
                + " Fis_at_red"
                + " from request_message_read"
                + " where Frequest_id = ? and Fcompany_info_id = ?  and Fis_read = 0";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, requestId);
            ps.setInt(2, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, requestId, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    MessageRead messageRead = new MessageRead();
                    messageRead.setMessageId(rs.getInt(1));
                    messageRead.setContactId(rs.getInt(2));
                    messageRead.setCompanyInfoId(rs.getInt(3));
                    messageRead.setRequestId(rs.getInt(4));
                    messageRead.setIsRefuse(rs.getInt(5));

                    messageRead.setIsAtRed(rs.getInt(6));
                    list.add(messageRead);
                }
            }
        }
        return list;
    }

    @Override
    public List<MessageRead> selectRedPointByRequestId(Integer requestId) throws SQLException {
        List<MessageRead> list = new ArrayList<>();
        String sql = "select Fmessage_id, Fcontact_id, Fcompany_info_id, Frequest_id, Fis_refuse,"
                + " Fis_at_red"
                + " from request_message_read"
                + " where Frequest_id = ? and Fis_read = 0";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, requestId);
            logger.info(SQLLogger.logSQL(sql, requestId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    MessageRead messageRead = new MessageRead();
                    messageRead.setMessageId(rs.getInt(1));
                    messageRead.setContactId(rs.getInt(2));
                    messageRead.setCompanyInfoId(rs.getInt(3));
                    messageRead.setRequestId(rs.getInt(4));
                    messageRead.setIsRefuse(rs.getInt(5));

                    messageRead.setIsAtRed(rs.getInt(6));
                    list.add(messageRead);
                }
            }
        }
        return list;
    }

    @Override
    public void updateMessageUnRead(RedPointDTO redPointDTO) throws SQLException {
        String sql = "update request_message_read set Fis_read = 0, Fread_time = null"
                + " where Fmessage_id = ? and Frequest_id = ? and Fcompany_info_id = ? limit 1";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, redPointDTO.getMessageId());
            ps.setInt(2, redPointDTO.getRequestId());
            ps.setInt(3, redPointDTO.getCompanyInfoId());
            logger.info(SQLLogger.logSQL(sql, redPointDTO.getMessageId(), redPointDTO.getRequestId(), redPointDTO.getCompanyInfoId()));

            ps.executeUpdate();
        }
    }

    @Override
    public Map<Integer, String> selectCommentReadTimeMap(Integer companyInfoId, String commentIds) throws SQLException {
        Map<Integer, String> map = new HashMap<>();
        String sql = "select Fmessage_id, Fread_time from request_message_read where Fcompany_info_id = ? and Fmessage_id in(" + commentIds + ")";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    map.put(rs.getInt(1), DateTimeHelper.format(rs.getTimestamp(2), DateTimeHelper.YEAR_MONTH_DAY_HOUR_MINUTE_PATTERN));
                }
            }
        }
        return map;
    }

    @Override
    public MessageRead selectMessageReadInfo(RedPointDTO redPointDTO) throws SQLException {
        String sql = "select Fmessage_id, Fcontact_id, Fcompany_info_id, Frequest_id, Fis_refuse,"
                + " Fis_at_red"
                + " from request_message_read"
                + " where Fmessage_id = ? and Frequest_id = ? and Fcompany_info_id = ? limit 1";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, redPointDTO.getMessageId());
            ps.setInt(2, redPointDTO.getRequestId());
            ps.setInt(3, redPointDTO.getCompanyInfoId());
            logger.info(SQLLogger.logSQL(sql, redPointDTO.getMessageId(), redPointDTO.getRequestId(), redPointDTO.getCompanyInfoId()));

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    MessageRead messageRead = new MessageRead();
                    messageRead.setMessageId(rs.getInt(1));
                    messageRead.setContactId(rs.getInt(2));
                    messageRead.setCompanyInfoId(rs.getInt(3));
                    messageRead.setRequestId(rs.getInt(4));
                    messageRead.setIsRefuse(rs.getInt(5));

                    messageRead.setIsAtRed(rs.getInt(6));
                    return messageRead;
                }
            }
        }
        return null;
    }

    @Override
    public List<Integer> getMyApplyInApproving(Integer companyId, Integer companyInfoId) throws SQLException {
        List<Integer> result = new LinkedList<>();

        String sql = " select Frequest_id from approval.request_flow where Fapplicant_ciid = ? and Ffinished in(-1,0)";

        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setInt(1, companyInfoId);

            logger.info(SQLLogger.logSQL(sql, companyInfoId));

            try (ResultSet rs = pstm.executeQuery()) {
                while (rs.next()) {
                    result.add(rs.getInt(1));
                }
            }
        }

        return result;
    }


    @Override
    public List<Integer> getMyApplyHasApproved(Integer companyId, Integer companyInfoId) throws SQLException {
        List<Integer> result = new LinkedList<>();

        String sql = " select Frequest_id from approval.request_flow"
                + "  force index(Fapplicant_ciid)"
                + " where Fcompany_id = ? and Ffinished in(-2,1,2,3) and Fapplicant_ciid = ? ";

        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setInt(1, companyId);
            pstm.setInt(2, companyInfoId);

            logger.info(SQLLogger.logSQL(sql, companyId, companyInfoId));

            try (ResultSet rs = pstm.executeQuery()) {
                while (rs.next()) {
                    result.add(rs.getInt(1));
                }
            }
        }
        return result;
    }


    @Override
    public Map<String, List<Integer>> getCcToMeMap(Integer companyId, Integer companyInfoId) throws SQLException {
        Map<String, List<Integer>> map = new HashMap<>();
        List<Integer> readList = new LinkedList<>();
        List<Integer> unReadList = new LinkedList<>();
        map.put(Constants.UN_READ, unReadList);
        map.put(Constants.READ, readList);

        String sql = " select Fis_read, Frequest_id"
                + " from approval.request_filed"
                + " force index(Fwho_filed_ciid)"
                + " where Fcompany_id = ? and Ffiled_status = 1 and Fstatus = 0 and Fwho_filed_ciid = ? ";

        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setInt(1, companyId);
            pstm.setInt(2, companyInfoId);

            logger.info(SQLLogger.logSQL(sql, companyId, companyInfoId));

            try (ResultSet rs = pstm.executeQuery()) {
                while (rs.next()) {
                    // Fis_read == 0: 表示未读
                    if (rs.getInt(1) == 0) {
                        unReadList.add(rs.getInt(2));
                    } else {
                        readList.add(rs.getInt(2));
                    }
                }
            }
        }

        return map;
    }


    @Override
    public List<Integer> getMyArchivedHasRed(Integer companyId, Integer companyInfoId, String requestIds) throws SQLException {
        List<Integer> result = new LinkedList<>();

        String sql = " select Frequest_id from approval.request_filed where Fcompany_id = ? " +
                " and Ffiled_status = 2 and Fstatus = 0 and Fwho_filed_ciid = ? " +
                " and Frequest_id in(" + requestIds + ")";

        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setInt(1, companyId);
            pstm.setInt(2, companyInfoId);

            logger.info(SQLLogger.logSQL(sql, companyId, companyInfoId));

            try (ResultSet rs = pstm.executeQuery()) {
                while (rs.next()) {
                    result.add(rs.getInt(1));
                }
            }
        }

        return result;
    }


    @Override
    public void clearRedPointByRequestIds(String requestIds, Integer companyInfoId) throws SQLException {

        String sql = " update approval.request_message_read set Fis_read = 1, Fread_time = now() " +
                " where Fcompany_info_id = ? and Fis_read = 0 and Frequest_id in(" + requestIds + ")";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyInfoId));

            ps.executeUpdate();
        }

    }


    @Override
    public void deleteUserRedPoint(Integer requestId, Integer companyInfoId) throws SQLException {

        String sql = " update approval.request_message_read set Fis_read = 1, Fread_time = now() " +
                " where Frequest_id = ? and Fis_read = 0 and Fcompany_info_id = ? ";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, requestId);
            ps.setInt(2, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, requestId, companyInfoId));

            ps.executeUpdate();
        }
    }

    @Override
    public void insertBatchRedPoints(List<MessageRead> messageReads) throws SQLException {
        String sql = " insert into request_message_read set"
                + " Fmessage_id = ?, Fcontact_id= ?, Fcompany_info_id = ?, Frequest_id = ?, Fcrt_by = ?,"
                + " Fis_refuse = ?, Fis_at_red = ?, Fcompany_id = ?, Fcreate_time = now() ";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            for (MessageRead messageRead : messageReads) {
                ps.setInt(1, messageRead.getMessageId());
                ps.setInt(2, messageRead.getContactId());
                ps.setInt(3, messageRead.getCompanyInfoId());
                ps.setInt(4, messageRead.getRequestId());
                ps.setInt(5, messageRead.getCreatorId());

                ps.setInt(6, messageRead.getIsRefuse());
                ps.setInt(7, messageRead.getIsAtRed());
                ps.setInt(8, messageRead.getCompanyId());
                logger.info(SQLLogger.logSQL(sql, messageRead.getMessageId(), messageRead.getContactId(),
                        messageRead.getCompanyInfoId(), messageRead.getRequestId(), messageRead.getCreatorId(),
                        messageRead.getIsRefuse(), messageRead.getIsAtRed(), messageRead.getCompanyId()));

                ps.addBatch();
            }
            ps.executeBatch();
        }
    }


}
